﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using Corsat.Library.Logic;

namespace Corsat.Library.DataManager
{
    public static partial class Main
    {

        #region Private Methods

        static int GenerateNewAccountCorsatNumber()
        {
            try
            {
                DataTable dtAllCorsatNumbers = GetData(StoredProcedure.sp_GetAllAccountsCorsatNumbers, null).Tables[0];

                Random random = new Random();
                int randomNumber = 0;

                bool IsNewNumber = false;

                do
                {
                    randomNumber = random.Next(1001, 9999);

                    DataRow[] drNumbers = dtAllCorsatNumbers.Select("CorsatId = " + randomNumber);

                    if (drNumbers.Length > 0)
                        IsNewNumber = false;
                    else
                        IsNewNumber = true;

                } while (!IsNewNumber);

                return randomNumber;
            }
            catch (Exception exc)
            {
                return 0;
            }
        }

        static string GenerateCoursesInsertStatement(List<ChosenCourse> Courses)
        {
            try
            {
                if (Courses.Count == 0)
                    return "";

                string CoursesInsertStatement = "";

                for (int i = 0; i < Courses.Count; i++)
                    CoursesInsertStatement += @"
                        EXEC sp_RegisterAccountCourse @AccountId, "
                        + Courses[i].CourseTitleId.ToString()
                        + ", "
                        + (Courses[i].CourseLevelId == 0 ? "NULL" : Courses[i].CourseLevelId.ToString());

                return CoursesInsertStatement;

            }
            catch (Exception exc)
            {
                return "";
            }
        }

        static string GenerateTelephonesInsertStatement(List<Telephone> Telephones)
        {
            try
            {
                if (Telephones.Count == 0)
                    return "";

                string telephonesQuery = "";

                for (int i = 0; i < Telephones.Count; i++)
                {
                    telephonesQuery += @" 
EXEC [" + StoredProcedure.sp_RegisterAccountTelephone.ToString() + "] @AccountId, " + (Telephones[i].Type.Id == 0 ? "NULL" : Telephones[i].Type.Id.ToString()) + ",'" + Telephones[i].Number + "'";
                }

                return telephonesQuery;
            }
            catch (Exception exc)
            {
                return "";
            }
        }

        static List<AccountCourse> GetAccountCourses(DataTable dtAccountCourses)
        {
            try
            {
                List<AccountCourse> Courses = new List<AccountCourse>();

                for (int i = 0; i < dtAccountCourses.Rows.Count; i++)
                {
                    DataRow drCourse = dtAccountCourses.Rows[i];

                    AccountCourse Course = new AccountCourse();
                    Course.CourseId = Convert.ToInt32(drCourse["CourseId"]);

                    Item CourseTitle = new Item();
                    CourseTitle.Id = Convert.ToInt32(drCourse["CourseTitleId"]);
                    CourseTitle.Value = drCourse["CourseTitleName"].ToString();
                    Course.CourseTitle = CourseTitle;

                    Item CourseLevel = new Item();
                    if (drCourse["CourseLevelId"] != DBNull.Value)
                    {
                        CourseLevel.Id = Convert.ToInt32(drCourse["CourseLevelId"]);
                        CourseLevel.Value = drCourse["CourseLevelName"].ToString();
                    }
                    Course.CourseLevel = CourseLevel;

                    if (drCourse["AccountSerialInCourse"] != DBNull.Value && drCourse["AccountSerialInCourse"] != null)
                        Course.AccountSerialInCourse = Convert.ToInt32(drCourse["AccountSerialInCourse"]);
                    else
                        Course.AccountSerialInCourse = null;

                    Course.Att_1 = (drCourse["Att_1"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_1"]));
                    Course.Att_2 = (drCourse["Att_2"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_2"]));
                    Course.Att_3 = (drCourse["Att_3"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_3"]));
                    Course.Att_4 = (drCourse["Att_4"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_4"]));
                    Course.Att_5 = (drCourse["Att_5"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_5"]));
                    Course.Att_6 = (drCourse["Att_6"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_6"]));
                    Course.Att_7 = (drCourse["Att_7"] == DBNull.Value ? false : Convert.ToBoolean(drCourse["Att_7"]));

                    //TODO: Handle the Result, IsLate, Grade Properties
                    float result;
                    float.TryParse(drCourse["Result"].ToString(), out result);
                    Course.Result = result;
                    Item grade = new Item();
                    if (drCourse["GradeId"] != DBNull.Value)
                    {
                        grade.Id = Convert.ToInt32(drCourse["GradeId"]);
                        grade.Value = drCourse["GradeName"].ToString();
                    }
                    Course.Grade = grade;

                    Item day = new Item();
                    if (drCourse["DayId"] != DBNull.Value)
                    {
                        day.Id = Convert.ToInt32(drCourse["DayId"]);
                        day.Value = drCourse["DayName"].ToString();
                    }
                    Course.Day = day;


                    Courses.Add(Course);
                }

                return Courses;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        static List<Telephone> GetAccountTelephones(DataTable dtAccountTelephones)
        {
            try
            {
                List<Telephone> Telephones = new List<Telephone>();

                for (int i = 0; i < dtAccountTelephones.Rows.Count; i++)
                {
                    DataRow drTelephone = dtAccountTelephones.Rows[i];
                    // Number, TeleTypeId, TeleTypeName
                    Telephone tele = new Telephone();
                    tele.Number = drTelephone["Number"] == DBNull.Value ? "" : drTelephone["Number"].ToString();

                    if (drTelephone["TeleTypeId"] != DBNull.Value)
                    {
                        Item Type = new Item();
                        Type.Id = Convert.ToInt32(drTelephone["TeleTypeId"]);
                        Type.Value = drTelephone["TeleTypeName"].ToString();

                        tele.Type = Type;
                    }

                    Telephones.Add(tele);
                }

                return Telephones;
            }
            catch (Exception exc)
            {
                return null;
            }
        }

        #endregion

        #region Public Methods

        public static int InsertAccount(Logic.Account Account)
        {
            try
            {
                command.CommandText = StoredProcedure.SP_InsertAccount.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();

                command.Parameters.Add(CreateParameter("ArabicName", Account.ArabicName));
                command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                command.Parameters.Add(CreateParameter("IsOnlineRegistered", Convert.ToInt32(Account.IsOnlineRegistered)));
                command.Parameters.Add(CreateParameter("IsActivated", Convert.ToInt32(Account.IsActivated)));

                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();

                int AccountId = Convert.ToInt32(objAccountId);

                return AccountId;
            }
            catch
            {
                return 0;
            }
        }

        public static int RegisterAccount(Logic.Account Account, List<ChosenCourse> Courses)
        {
            try
            {
                int CorsatId = 0;
                CorsatId = GenerateNewAccountCorsatNumber();

                if (CorsatId == 0) return 0;


                command.CommandType = CommandType.StoredProcedure;

                if (Account.AccountId == 0)
                    command.CommandText = StoredProcedure.SP_InsertAccount.ToString();
                else
                    command.CommandText = StoredProcedure.sp_UpdateAccount.ToString();

                command.Parameters.Clear();

                if (Account.AccountId != 0) command.Parameters.Add(CreateParameter("AccountId", Account.AccountId));
                command.Parameters.Add(CreateParameter("ArabicName", Account.ArabicName));
                command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                command.Parameters.Add(CreateParameter("IsOnlineRegistered", Convert.ToInt32(Account.IsOnlineRegistered)));
                command.Parameters.Add(CreateParameter("IsActivated", Convert.ToInt32(Account.IsActivated)));

                // Handling the case that there is no church id, or its correspondent of having the ChurchId = 0.
                object ChurchId = DBNull.Value;
                if (Account.Church.Id != 0)
                    ChurchId = Account.Church.Id;

                command.Parameters.Add(CreateParameter("ChurchId", ChurchId));

                // Similarly, handling the DioceseId.
                object DioceseId = DBNull.Value;
                if (Account.Diocese.Id != 0)
                    DioceseId = Account.Diocese.Id;

                command.Parameters.Add(CreateParameter("DioceseId", DioceseId));

                // Similarly, handling the StudyId.
                object StudyId = DBNull.Value;
                if (Account.Study.Id != 0)
                    StudyId = Account.Study.Id;

                command.Parameters.Add(CreateParameter("StudyId", StudyId));

                // Similarly, handling the StudyId.
                object ProfessionId = DBNull.Value;
                if (Account.Profession.Id != 0)
                    ProfessionId = Account.Profession.Id;

                command.Parameters.Add(CreateParameter("ProfessionId", ProfessionId));


                command.Parameters.Add(CreateParameter("Email", Account.Email));
                command.Parameters.Add(CreateParameter("BirthDate", Account.BirthDate));
                command.Parameters.Add(CreateParameter("Year", DateTime.Now.Year));
                command.Parameters.Add(CreateParameter("CorsatId", CorsatId));

                var queryComplementary = @"

DECLARE @AccountId INT

SET @AccountId = (SELECT top(1) AccountsMaster.AccountId FROM AccountsMaster INNER JOIN AccountsDetails ON AccountsMaster.AccountId = AccountsDetails.AccountId WHERE AccountsMaster.SSN = @SSN AND AccountsDetails.Year = " + DateTime.Today.Year.ToString() + @")
        
";

                // In case that the Account is being updated.
                if (Account.AccountId > 0) queryComplementary += "DELETE FROM AccountCourses WHERE AccountId = " + Account.AccountId.ToString() + " AND [Year] = " + DateTime.Now.Year.ToString() + "\n";

                queryComplementary += GenerateCoursesInsertStatement(Courses);

                // In case that the account is being updated.
                if (Account.AccountId > 0) queryComplementary += "\n" + "DELETE FROM AccountTelephones WHERE AccountId = " + Account.AccountId.ToString() + "\n";

                queryComplementary += GenerateTelephonesInsertStatement(Account.Telephones);

                OpenConnection();
                //object objAccountId = command.ExecuteScalar();
                SqlDataReader reader = command.ExecuteReader();
                object objAccountId = null;
                if (reader.Read())
                {
                    objAccountId = reader["CorsatId"];
                    object savedAccountId = reader["AccountId"];
                    int accountId;
                    if (savedAccountId!=null && int.TryParse(savedAccountId.ToString(),out accountId) && accountId>0)
                    {
                        Account.AccountId = accountId;
                    }
                }
                reader.Close();
                if (queryComplementary.Trim().Length != 0)
                {
                    command.Parameters.Clear();

                    command.CommandText = queryComplementary;
                    command.Parameters.Add(CreateParameter("SSN", Account.SSN));
                    command.CommandType = CommandType.Text;

                    command.ExecuteNonQuery();
                }

                CloseConnection();

                if (objAccountId == DBNull.Value || Convert.ToInt32(objAccountId) == 0)
                    return 0;
                else
                    return Convert.ToInt32(objAccountId);
            }
            catch (Exception exc)
            {
                return 0;
            }
        }

        public static Logic.Account GetAccountById(int AccountId)
        {
            try
            {
                // Get the DataSet containing all the Account's Data.
                // Table 1 : AccountsMaster Data
                // Table 2 : AccountsDetails Data
                // Table 3 : AccountTelephones Data
                // Table 4 : AccountCourses Data
                DataSet dsAccount = GetData(StoredProcedure.sp_SelectAccount, new SqlParameter[] { new SqlParameter("@AccountId", AccountId) });

                Logic.Account account = new Logic.Account();

                DataRow drAccountsMaster = dsAccount.Tables[0].Rows[0];

                account.AccountId = AccountId;
                account.ArabicName = drAccountsMaster["ArabicName"].ToString();
                account.BirthDate = Convert.ToDateTime(drAccountsMaster["BirthDate"]);
                account.Email = drAccountsMaster["Email"].ToString();

                account.SSN = drAccountsMaster["SSN"].ToString();

                // Load the Diocese .. added new in 2013 ??????
                if (drAccountsMaster["DioceseId"] != DBNull.Value)
                {
                    Item Diocese = new Item();
                    Diocese.Id = Convert.ToInt32(drAccountsMaster["DioceseId"]);
                    Diocese.Value = drAccountsMaster["DioceseName"].ToString();
                    account.Diocese = Diocese;
                }

                // Load the church .. added new in 2013 ??????
                if (drAccountsMaster["ChurchId"] != DBNull.Value)
                {
                    Item Church = new Item();
                    Church.Id = Convert.ToInt32(drAccountsMaster["ChurchId"]);
                    Church.Value = drAccountsMaster["ChurchName"].ToString();
                    account.Church = Church;
                }
                if (dsAccount.Tables[1].Rows.Count > 0)
                {
                    DataRow drAccountsDetails = dsAccount.Tables[1].Rows[0];

                    account.CorsatId = drAccountsDetails["CorsatId"] == DBNull.Value ? 0 : Convert.ToInt32(drAccountsDetails["CorsatId"]);
                    account.IsOnlineRegistered = drAccountsDetails["IsOnlineRegistered"] == DBNull.Value ? false : Convert.ToBoolean(drAccountsDetails["IsOnlineRegistered"]);
                    account.IsActivated = drAccountsDetails["IsActivated"] == DBNull.Value ? false : Convert.ToBoolean(drAccountsDetails["IsActivated"]);
                    account.ConferenceStatusId = drAccountsDetails["ConferenceStatusId"] == DBNull.Value ? 0 : Convert.ToInt32(drAccountsDetails["ConferenceStatusId"]);

                    if (drAccountsDetails["TechnologyCourseId"] != DBNull.Value)
                    {
                        AccountTechnologyCourse Tech = new AccountTechnologyCourse();

                        Item Course = new Item();
                        Course.Id = Convert.ToInt32(drAccountsDetails["TechnologyCourseId"]);
                        Course.Value = Convert.ToString(drAccountsDetails["TechnologyCourseName"]);
                        Tech.Course = Course;

                        if (drAccountsDetails["TechnologyCourseGradeId"] != DBNull.Value)
                        {
                            Item techgrade = new Item();
                            techgrade.Id = Convert.ToInt32(drAccountsDetails["TechnologyCourseGradeId"]);
                            techgrade.Value = Convert.ToString(drAccountsDetails["TechnologyCourseGradeName"]);
                            Tech.Grade = techgrade;
                        }

                        if (drAccountsDetails["TechnologyCourseTestDate"] != DBNull.Value)
                            Tech.ExamDate = Convert.ToDateTime(drAccountsDetails["TechnologyCourseTestDate"]);

                        if (drAccountsDetails["TechnologyCourseExamLab"] != DBNull.Value)
                            Tech.Lab = Convert.ToChar(drAccountsDetails["TechnologyCourseExamLab"]);

                        if (drAccountsDetails["TechnologyCourseExamReservationDate"] != DBNull.Value)
                            Tech.ReservationDate = Convert.ToDateTime(drAccountsDetails["TechnologyCourseExamReservationDate"]);

                        if (drAccountsDetails["TechnologyCourseExamPeriod"] != DBNull.Value)
                            Tech.Period = Convert.ToInt32(drAccountsDetails["TechnologyCourseExamPeriod"]);

                        if (drAccountsDetails["TechnologyCourseResult"] != DBNull.Value)
                            Tech.Result = float.Parse(drAccountsDetails["TechnologyCourseResult"].ToString());

                        account.TechnologyCourse = Tech;
                    }
                    if (drAccountsDetails["ConferenceBookingDate"] != DBNull.Value)
                    {
                        account.ConferenceBookingDate = Convert.ToDateTime(drAccountsDetails["ConferenceBookingDate"]);
                    }
                    if (drAccountsDetails["ConferenceCompeleteBookingDate"] != DBNull.Value)
                    {
                        account.ConferenceCompeleteBookingDate =
                            Convert.ToDateTime(drAccountsDetails["ConferenceCompeleteBookingDate"]);
                    }

                }

                // Get the Account Telephones
                if (dsAccount.Tables[2].Rows.Count > 0)
                    account.Telephones = GetAccountTelephones(dsAccount.Tables[2]);

                // Get the User Registered Courses
                if (dsAccount.Tables[3].Rows.Count > 0)
                    account.Courses = GetAccountCourses(dsAccount.Tables[3]);


                return account;


            }
            catch (Exception exc)
            {
                return new Logic.Account();
            }
        }

        public static bool ActivateAccount(int AccountId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_ActivateAccount.ToString();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("AccountId", AccountId));

                OpenConnection();
                object NoOfRows = command.ExecuteNonQuery();
                CloseConnection();

                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static bool RegisterAccountCourse(int AccountId, int CourseTitleId, int CourseLevelId)
        {
            try
            {
                command.CommandText = " EXEC sp_RegisterAccountCourse " + AccountId.ToString() + @", "
                         + CourseTitleId.ToString()
                         + ", "
                         + (CourseLevelId == 0 ? "NULL" : CourseLevelId.ToString());

                command.CommandType = CommandType.Text;
                command.Parameters.Clear();

                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();

                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static bool UnRegisterAccountCourse(int AccountId, int CourseId)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_UnregisterAccountCourse.ToString();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("AccountId", AccountId));
                command.Parameters.Add(new SqlParameter("CourseId", CourseId));
                command.Parameters.Add(new SqlParameter("Year", DateTime.Today.Year));

                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();

                return true;
            }
            catch (Exception exc)
            {
                return false;
            }

        }

        public static List<int> GetPassedCourses(string SSN, int Year)
        {
            try
            {
                List<int> PassedCourses = new List<int>();

                command.CommandText = StoredProcedure.sp_GetAccountHistoryCourses.ToString();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("SSN", SSN));
                command.Parameters.Add(new SqlParameter("Year", Year));

                DataTable dtResults = new DataTable();

                adapter.Fill(dtResults);

                for (int i = 0; i < dtResults.Rows.Count; i++)
                {
                    if (dtResults.Rows[i]["GradeId"] != DBNull.Value)
                    {
                        int GradeId = Convert.ToInt32(dtResults.Rows[i]["GradeId"]);
                        if ((new List<int> { 312, 313, 314, 315, 317 }).Contains(GradeId))
                            PassedCourses.Add(Convert.ToInt32(dtResults.Rows[i]["CourseId"]));
                    }
                }

                return PassedCourses;
            }
            catch (Exception exc)
            {
                return new List<int>();
            }
        }

        public static Logic.Account GetAccountBySSN(string SSN)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_GetAccountIdBySSN.ToString();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("SSN", SSN));

                OpenConnection();
                object accountIdObj = command.ExecuteScalar();
                CloseConnection();

                if (accountIdObj != null)
                {
                    Logic.Account account = new Logic.Account(Convert.ToInt32(accountIdObj));

                    if (account.AccountId == 0 && Convert.ToInt32(accountIdObj) != 0) account.AccountId = Convert.ToInt32(accountIdObj);

                    return account;
                }

                return new Logic.Account();
            }
            catch (Exception exc)
            {
                return null;
            }
        }



        public static bool UpdateAccountConferenceStatus(int accountId, ConferenceStatus status, int year, DateTime? conferenceBookingDate, DateTime? conferenceCompeleteBookingDate)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_UpdateAccountConferenceStatus.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                if (accountId > 0)
                {
                    command.Parameters.Add(CreateParameter("@AccountId", accountId));
                }
                if (year > 0)
                {
                    command.Parameters.Add(CreateParameter("@Year", year));
                }
                if (status >= ConferenceStatus.NotBooked && status <= ConferenceStatus.CompeleteBooking)
                {
                    command.Parameters.Add(CreateParameter("@ConferenceStatusId", (int)status));
                }
                if (conferenceBookingDate != null)
                {
                    command.Parameters.Add(CreateParameter("@ConferenceBookingDate", conferenceBookingDate.Value.Date));
                }
                if (conferenceCompeleteBookingDate != null)
                {
                    command.Parameters.Add(CreateParameter("@ConferenceCompeleteBookingDate", conferenceCompeleteBookingDate.Value.Date));
                }

                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();


                return true;
            }
            catch
            {
                return false;
            }
        }


        public static bool UpdateAccountData(Account account)
        {
            try
            {
                command.CommandText = StoredProcedure.sp_UpdateAccountData.ToString();
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Clear();
                
                command.Parameters.Add(CreateParameter("@AccountId", account.AccountId));
                command.Parameters.Add(CreateParameter("@ArabicName", account.ArabicName));
                command.Parameters.Add(CreateParameter("@SSN", account.SSN));
                command.Parameters.Add(CreateParameter("@Email", account.Email));


                DataTable dt = new DataTable();
                dt.Columns.Add("Number");
                dt.Columns.Add("AccountId");
                dt.Columns.Add("TeleTypeId");
                foreach (Telephone telephone in account.Telephones)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = telephone.Number;
                    dr[1] = account.AccountId;
                    dr[2] = telephone.Type.Id;
                    dt.Rows.Add(dr);
                }
                command.Parameters.Add(CreateParameter("@Telephones", dt));

                OpenConnection();
                object objAccountId = command.ExecuteScalar();
                CloseConnection();


                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion
    }
}
